Namespace persistent
    Public Class ResourceStatusUpdate

        Public Shared Function selectStorageMedium(ByVal serialnum As String) As DataTable
            Dim SQL As String

            SQL = "select * from storage_medium where sm_serialnum='" & serialnum & "'"

            Return myDBConn.myORATable(SQL)
        End Function

        Public Shared Function selectSpecStorageMedium(ByVal serialnum As String) As DataTable
            Dim SQL As String

            SQL = "select sm_serialnum sm,sm_status sm_stat,null dnumber,null dn_stat from storage_medium where sm_serialnum='" & serialnum & "'"

            Return myDBConn.myORATable(SQL)
        End Function

        Public Shared Function selectDirectoryNum(ByVal dnnum As String) As DataTable
            Dim SQL As String

            SQL = "select * from eu_bill.view_directory_number where dn_num='" & dnnum & "'"

            Return myDBConn.myORATable(SQL)
        End Function

        Public Shared Function selectSpecDirectoryNum(ByVal dnnum As String) As DataTable
            Dim SQL As String

            SQL = "select null sm,null sm_stat,dn_num dnumber,dn_status dn_stat from eu_bill.view_directory_number where dn_num='" & dnnum & "'"

            Return myDBConn.myORATable(SQL)
        End Function

        Public Shared Function select1(ByVal serialnum As String) As DataTable
            Dim strsql As String

            strsql = " select sm.sm_serialnum sm,sm_status sm_stat,dn.dn_num dnumber,dn.dn_status dn_stat"
            strsql = strsql & " from storage_medium sm, port p, eu_bill.view_directory_number dn"
            strsql = strsql & " where sm.sm_id=p.port_id"
            strsql = strsql & " and p.dn_id=dn.dn_id"
            strsql = strsql & " and sm.sm_id=(select sm_id  from storage_medium where sm_serialnum='" & serialnum & "')"

            Return myDBConn.myORATable(strsql)
        End Function

        Public Shared Function select2(ByVal dnnum As String) As DataTable
            Dim strsql As String

            strsql = " select p.port_id"
            strsql = strsql & " from  port p, eu_bill.view_directory_number dn"
            strsql = strsql & " where p.dn_id=dn.dn_id"
            strsql = strsql & " and dn.dn_num='" & dnnum & "'"

            Return myDBConn.myORATable(strsql)
        End Function

        Public Shared Function select3(ByVal smsID As String) As DataTable
            Dim strsql As String

            strsql = " select sm.sm_serialnum sm,sm_status sm_stat,dn.dn_num dnumber,dn.dn_status dn_stat"
            strsql = strsql & " from storage_medium sm, port p, eu_bill.view_directory_number dn"
            strsql = strsql & " where sm.sm_id=p.port_id"
            strsql = strsql & " and p.dn_id=dn.dn_id"
            strsql = strsql & " and sm.sm_id=" & smsID & ""

            Return myDBConn.myORATable(strsql)
        End Function

        Public Shared Function select4(ByVal serialNum As String) As DataTable
            Dim strsql As String

            strsql = " select count(cap.co_id) from storage_medium sm,contr_devices cd,contr_services_cap cap"
            strsql = strsql & " where sm.sm_id=cd.port_id"
            strsql = strsql & " and cd.co_id=cap.co_id"
            strsql = strsql & " and cd.co_id=(select max(co_id) from contr_devices cd1 where cd.port_id=cd1.port_id)"
            strsql = strsql & " and sm.sm_serialnum='" & serialNum & "'"

            Return myDBConn.myORATable(strsql)
        End Function

        Public Shared Function select5(ByVal dnNum As String) As DataTable
            Dim strsql As String

            strsql = " select cd.cd_seqno from eu_bill.view_directory_number  dn,contr_services_cap cap,contr_devices cd"
            strsql = strsql & " where cap.dn_id=dn.dn_id"
            strsql = strsql & " and cap.co_id=cd.co_id"
            strsql = strsql & " and cd.CD_SEQNO=(select max(cd1.CD_SEQNO) from contr_devices cd1  where cd.co_id=cd1.co_id)"
            strsql = strsql & " and cap.CS_ACTIV_DATE=(select max(cap1.cs_activ_date) from contr_services_cap cap1 where cap.dn_id=cap1.dn_id)"
            strsql = strsql & " and dn.dn_num='" & dnNum & "'"

            Return myDBConn.myORATable(strsql)
        End Function

        Public Shared Function updateStorageMedium(ByVal serialNum As String) As Long
            Dim SQL As String
            SQL = " update storage_medium set sm_status='r',sm_status_mod_date=sysdate "
            SQL = SQL & "  where sm_serialnum='" & Trim(serialNum) & "'"
            Return myDBConn.ORAExecute(SQL)
        End Function
        Public Shared Function updatePort(ByVal serialNum As String) As Long
            Dim SQL As String
            SQL = " update port   set port_status='r',port_statusmoddat=sysdate"
            SQL = SQL & " where port_id in "
            SQL = SQL & " (select sm_id from storage_medium where sm_serialnum='" & serialNum & "')"
            Return myDBConn.ORAExecute(SQL)
        End Function
        Public Shared Function updateDirNum(ByVal dnNum As String) As Long
            Dim SQL As String
            SQL = " update eu_bill.view_directory_number set dn_status='r',dn_status_mod_date=sysdate"
            SQL = SQL & " where dn_num='" & dnNum & "'"
            Return myDBConn.ORAExecute(SQL)
        End Function

        Public Shared Function insertLog(ByVal resource_no As String, ByVal user As String) As Long
            Dim SQL As String
            SQL = "insert into ra_update_log (resource_no,update_user) values ('" & resource_no & "','" & user & "');"

            Return myDBConn.Execute(SQL)

        End Function

        Public Shared Function insertLinkLog(ByVal resource_no As String, ByVal user As String) As Long
            Dim SQL As String
            SQL = "insert into  break_link_log (resource_no,update_user) values ('" & resource_no & "','" & user & "');"

            Return myDBConn.Execute(SQL)

        End Function

        Public Shared Function updatePort2(ByVal dn_num As String) As Long
            Dim SQL As String
            SQL = "  update port set dn_id =null"
            SQL = SQL & "  where dn_id =(select dn_id from eu_bill.view_directory_number where dn_num='" & dn_num & "')"

            Return myDBConn.ORAExecute(SQL)
        End Function

    End Class
End Namespace
